IF EXISTS (SELECT *
           FROM INFORMATION_SCHEMA.ROUTINES
           WHERE ROUTINE_NAME='pOutProdDetail_Update'
           AND ROUTINE_SCHEMA='dbo'
           AND ROUTINE_TYPE='PROCEDURE'
          )
          DROP PROCEDURE dbo.pOutProdDetail_Update
GO

CREATE PROC dbo.pOutProdDetail_Update
	@productId INT,
	@amount INT,
	@outPaperDetailId INT,
	@returnValue INT OUT
AS
/*@@*_____________________________________________________________________

  

_____________________________________________________________________*@@*/

/*______________________ CONSTANT DECLARATIONS _________________________*/


/*______________________ VARIABLE DECLARATIONS _________________________*/

  DECLARE
    @intErrNo       INT,
    @strErrMsg      NVARCHAR(400),
    @intReturn      INT,
    @internalAmount INT,
    @oldAmount		INT,
    @oldProductId	INT,
    @remainAmount	INT,
    @inProdDetailId INT,
    @internalRemain	INT,
    @check			INT,
	@id				INT

/*_______________________________ INIT _________________________________*/

  -- Setting NOCOUNT ON prevents additional recordsets with the
  -- "number of records affected" from being returned, which is not
  -- handled the same by all providers, such as for OLEDB vs. ODBC.
  SET NOCOUNT ON

/*________________________ VALIDATE PARAMETERS _________________________*/


/*_______________________________ MAIN _________________________________*/
	
	SELECT @oldAmount = Amount,
		@oldProductId = ProductId
	FROM InOutProdDetail
	WHERE Id = @outPaperDetailId
							
	SET @internalAmount = (SELECT Product.Amount FROM Product WHERE Id = @productId)
						
	IF (@oldProductId = @productId AND (@oldAmount + @internalAmount >= @amount))
		OR ((@oldProductId <> @productId) AND (@internalAmount >= @amount)) 
	BEGIN 
		SET @check = 1
	END	
	ELSE BEGIN
		SET @check = 2
	END										
							
	IF(@check = 1) BEGIN
		BEGIN TRAN UpdateProdDetail
		BEGIN TRY		
		
		-- Add old amount (simlar to delete case)
		SET @remainAmount = @oldAmount
		WHILE (@remainAmount) >0 BEGIN
			SELECT TOP 1 
				@inProdDetailId = Id,
				@internalRemain = RemainAmount,
				@internalAmount = Amount
			FROM InOutProdDetail 
				WHERE RemainAmount < Amount AND ProductId = @oldProductId
				ORDER BY Id DESC
			
			IF @inProdDetailId IS NOT NULL BEGIN
				IF (@internalAmount - @internalRemain) > @remainAmount BEGIN 
					UPDATE InOutProdDetail
					SET RemainAmount = (RemainAmount + @remainAmount)
					WHERE Id = @inProdDetailId
					
					SET @remainAmount = 0
				END						
				ELSE BEGIN
					UPDATE InOutProdDetail
					SET RemainAmount = Amount
					WHERE Id = @inProdDetailId
					
					SET @remainAmount = (@remainAmount - (@internalAmount- @internalRemain))
				END
				
				-- Must reset the value of variables if not it keep the data of last loop
				SET @inProdDetailId = NULL
				SET @internalRemain = 0
				SET @internalAmount = 0
			END
			ELSE BEGIN
				SET @remainAmount = 0 -- break while
			END
		END
		
		-- Minus new amount (similar to add case)
		SET @remainAmount = @amount
		PRINT(@remainAmount)
		WHILE @remainAmount >0 BEGIN
			SELECT TOP 1 
				@inProdDetailId = Id,
				@internalRemain =  RemainAmount
			FROM InOutProdDetail 
			WHERE RemainAmount > 0 AND ProductId = @productId 
			ORDER BY Id
			
			IF @inProdDetailId IS NOT NULL BEGIN
				IF @internalRemain >= @remainAmount BEGIN
					UPDATE InOutProdDetail
					SET RemainAmount = (RemainAmount - @remainAmount)
					WHERE Id = @inProdDetailId
					SET @remainAmount = 0
				END
				ELSE BEGIN 
					UPDATE InOutProdDetail
					SET RemainAmount = 0
					WHERE Id = @inProdDetailId
					SET @remainAmount = (@remainAmount - @internalRemain)
				END
				PRINT(@remainAmount)
				
				-- Must reset the value of variables if not it keep the data of last loop
				SET @inProdDetailId = NULL
				SET @internalRemain = 0
			END
			ELSE BEGIN
				SET @remainAmount = 0 -- break while
			END
		END		
		
		UPDATE InOutProdDetail
		SET Amount = @amount, ProductId = @productId
		WHERE Id = @outPaperDetailId		
		
		-- Update amount of old product
		UPDATE Product
		SET Amount = (Amount + @oldAmount)
		WHERE Id = @oldProductId
		
		-- Update amount of new product
		UPDATE Product
		SET Amount = (Amount - @amount)
		WHERE Id = @productId
		
		-- Fill BillDetail_InProd detail by new In_Prod Detail (old product)
		EXEC pUpdateBillDetail_InProdDetail_AfterInDetailChange
			@vintProductId = @oldProductId
		
		-- Fill BillDetail_InProd detail by new In_Prod Detail (new product)		
		EXEC pUpdateBillDetail_InProdDetail_AfterInDetailChange
			@vintProductId = @productId
		
		SET @intReturn = 0	
		COMMIT TRANSACTION UpdateProdDetail

		END TRY
		BEGIN CATCH
			SET @intReturn = -2
		    ROLLBACK TRANSACTION UpdateProdDetail
		END CATCH  

	END
	ELSE
	BEGIN
		SET @intReturn = -1				
	END
						
	SET @returnValue = @intReturn
							
  -- Return value 0 indicates success
  
lblExit:
	RETURN @intReturn
/*___________________________ ERROR HANDLER ____________________________*/
lblError:
  -- An error has occurred.  On entering this section @intErrNo should
  -- already hold the error number

  -- By default, if there are any open transactions then roll them back
  IF (@@TRANCOUNT > 0) BEGIN
    ROLLBACK TRAN

  END

  -- If return value has not been explicitly set, then use the error
  -- number as return value to signify failure
  IF ((@intReturn IS NULL) OR (@intReturn = 0)) BEGIN
    SET @intReturn = @intErrNo

  END

 GOTO lblExit


